package dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import domain.Document;
import domain.Vocabulary;

public class DocumentDAO extends GenericDAO<Document> {
	public static final String TABLE_NAME = "documento";
	private Document document;

	public DocumentDAO() throws ClassNotFoundException, IOException {
		super();
		table = TABLE_NAME;
	}

	@Override
	/*
	 * Inserts a document in the database
	 * @param value, the document to insert
	 */
	protected void onInsert(Document value) throws SQLException {
		query = new StringBuilder("");
		document = value;
		query.append("insert into ");
		query.append(table);
		query.append(" values (?,?,?,?,?,?)");
		Connection conn = getConnection(); 
		statement = conn.prepareStatement(query.toString());
		statement.setInt(1, 0);
		statement.setString(2, document.getName());
		statement.setString(3, document.getPath());
		statement.setInt(4, document.getNumberOfWords());
		statement.setInt(5, document.getTotalOfDocuments());
		statement.setLong(6, document.getFileSize());
		statement.executeUpdate();
		
		closeConnection(conn);
	}

	@Override
	/*
	 * Selects a document from the database, which match with the id given
	 * @param id, the id of the document to get
	 * @return doc, the document with the id given by parameter
	 */
	protected Document onSelect(Integer id) throws SQLException {
		Document doc = new Document();
		query = new StringBuilder("");
		query.append("select id_documento, nombre, path, size_documento, cantidad_total_documentos, size_archivo from ");
		query.append(table);
		query.append(" where id_documento = ?");
		Connection conn = getConnection(); 
		statement = conn.prepareStatement(query.toString());
		statement.setInt(1, id);
		rs = statement.executeQuery();
		while (rs.next()) {
			doc.setId(rs.getInt(1));
			doc.setName(rs.getString(2).trim());
			doc.setPath(rs.getString(3).trim());
			doc.setNumberOfWords(rs.getInt(4));
			doc.setTotalOfDocuments(rs.getInt(5));
			doc.setFileSize(rs.getLong(6));
		}
		
		closeConnection(conn);
		return doc;
	}

	@Override
	/*
	 * Selects the documents from the database, which match with the attributes of the
	 * document given
	 * @param value, the document with the attributes with which must match the collection
	 * of documents returned
	 * @return collection, a collection of documents
	 */
	protected ArrayList<Document> onSelect(Document value) throws SQLException {
		ArrayList<Document> collection = new ArrayList<Document>();
		Document doc = null;
		query = new StringBuilder("");
		query.append("select id_documento, nombre, path, size_documento, cantidad_total_documentos, size_archivo from ");
		query.append(table);
		Connection conn = getConnection();
		prepareUpdateQuery(conn, value, query);
		rs = statement.executeQuery();
		while (rs.next()) {
			doc = new Document();
			doc.setId(rs.getInt(1));
			doc.setName(rs.getString(2));
			doc.setPath(rs.getString(3));
			doc.setNumberOfWords(rs.getInt(4));
			doc.setTotalOfDocuments(rs.getInt(5));
			doc.setFileSize(rs.getLong(6));
			collection.add(doc);
		}
		
		closeConnection(conn);
		return collection;
	}

	@Override
	/*
	 * Updates a document in the database, with the values of the document passed by
	 * parameter
	 * @param value, the document to update
	 */
	protected void onUpdate(Document value) throws SQLException {
		query = new StringBuilder("");
		query.append("update ");
		query.append(table);
		query.append(" set nombre=?, path=?, size_documento=?, cantidad_total_documentos=?, size_archivo=?");
		query.append(" where id=?");
		Connection conn = getConnection();
		statement = conn.prepareStatement(query.toString());
		statement.setString(1, value.getName());
		statement.setString(2, value.getPath());
		statement.setInt(3, value.getNumberOfWords());
		statement.setInt(4, value.getTotalOfDocuments());
		statement.setLong(5, value.getFileSize());
		statement.setInt(6, value.getId());
		statement.executeUpdate();
		
		closeConnection(conn);
	}

	public int count() throws SQLException {
		int count = 0;
		query = new StringBuilder("");
		query.append("SELECT count(id_documento) from ");
		query.append(table);
		Connection conn = getConnection();
		statement = conn.prepareStatement(query.toString());
		rs = statement.executeQuery();
		if (rs.next()) {
			count = rs.getInt(1);
		}
		closeConnection(conn);
		return count;
	}

	public List<Document> select(Vocabulary vocabulary) throws SQLException {
		List<Document> collection = new ArrayList<Document>();
		Document doc = null;
		query = new StringBuilder("");
		query.append("SELECT DISTINCT d.id_documento, d.nombre, d.path, d.size_documento, d.cantidad_total_documentos, d.size_archivo from ");
		query.append(table + " d, ");
		query.append(VocabularyDAO.TABLE_NAME + " v, ");
		query.append(PostDAO.TABLE_NAME + " p");
		query.append(" WHERE ");
		query.append("d.id_documento = p.id_documento");
		query.append(" AND ");
		query.append("p.id_vocabulario = v.id_vocabulario");
		query.append(" AND ");
		query.append("v.termino = ?");
		Connection conn = getConnection(); 
		statement = conn.prepareStatement(query.toString());
		statement.setString(1, vocabulary.getWord());
		rs = statement.executeQuery();
		while (rs.next()) {
			doc = new Document();
			doc.setId(rs.getInt(1));
			doc.setName(rs.getString(2));
			doc.setPath(rs.getString(3));
			doc.setNumberOfWords(rs.getInt(4));
			doc.setTotalOfDocuments(rs.getInt(5));
			doc.setFileSize(rs.getLong(6));
			collection.add(doc);
		}
		
		closeConnection(conn);
		return collection;
	}

	/**
	 * prepares the query and the statement according with the non null values of the
	 * document given by parameter
	 * @param value, the document with the attributes used to build the filter
	 * @param statement, the statement to perform the query
	 * @param query, the query string
	 * @throws SQLException
	 */
	protected final void prepareUpdateQuery(Connection conn, Document value,
			StringBuilder query) throws SQLException {
		String auxQuery = "";
		ArrayList<String> dbColumns = new ArrayList<String>();
		ArrayList<Object> index = new ArrayList<Object>();
		if (value.getId() != null) {
			dbColumns.add("id");
			index.add(value.getId());
		}
		if (value.getName() != null && !value.getName().isEmpty()) {
			dbColumns.add("nombre");
			index.add(value.getName());
		}
		if (value.getPath() != null && !value.getPath().isEmpty()) {
			dbColumns.add("path");
			index.add(value.getPath());
		}
		if (value.getNumberOfWords() != null) {
			dbColumns.add("size_documento");
			index.add(value.getNumberOfWords());
		}
		if (value.getTotalOfDocuments() != null) {
			dbColumns.add("cantidad_total_documentos");
			index.add(value.getTotalOfDocuments());
		}
		if (value.getFileSize() != null) {
			dbColumns.add("size_archivo");
			index.add(value.getFileSize());
		}
		if (dbColumns.size() > 0) {
			auxQuery = " where " + dbColumns.get(0).toString() + "=?";
			for (int i = 1; i < dbColumns.size(); i++) {
				auxQuery += " and " + dbColumns.get(i).toString() + "=?";
			}
		}
		query.append(auxQuery);
		this.statement = conn.prepareStatement(query.toString());
		prepareUpdateStatement(index);
	}
}
